package com.ingenio.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.ingenio.Adapter;
import com.ingenio.business.Rubro;
import com.ingenio.business.SubRubro;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

public class SubRubroAdapter extends Adapter{
	
	public void agregarSubRubro(SubRubro subRubro, Connection conn) throws Exception{
		PreparedStatement stmt = null;
		try {
	    	String add ="INSERT into subrubro (descSubRubro, IdRubro, subRubHabilitado) " +
					"VALUES (?, ?, 1)";
			stmt = (PreparedStatement) conn.prepareStatement(add);
			stmt.setString(1, subRubro.getDecSubRubro());
			stmt.setInt(2, subRubro.getRubro().getIdRubro());
			stmt.execute();
    	}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
	}
	
	public void agregarSubRubro(Rubro rubro, Connection conn) throws Exception{
		PreparedStatement stmt = null;
		try {
	    	for (SubRubro sr : rubro.getSubrubros()) {
	    		String add ="INSERT into subrubro (descSubRubro, IdRubro, subRubHabilitado) " +
						"VALUES (?, ?, 1)";
				stmt = (PreparedStatement) conn.prepareStatement(add);
				stmt.setString(1, sr.getDecSubRubro());
				stmt.setInt(2, rubro.getIdRubro());
				stmt.execute();
			}    	
		}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
	}
	
	public void eliminarSubRubro(SubRubro subRubro) throws Exception{
    	Connection conn = null;
    	PreparedStatement stmt = null;
    	try {
	    	conn = (Connection) abrirConexion();
	    	String del ="UPDATE subrubro SET subRubHabilitado = 0 WHERE IdSubRubro = ?";
			stmt = (PreparedStatement) conn.prepareStatement(del);
			stmt.setInt(1, subRubro.getIdSubRubro());
			stmt.execute();
		}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		cerrarConexion(conn);
    	}
    }
	
	public void eliminarSubRubros(List<SubRubro> subrubros, Connection conn) throws Exception{
    	PreparedStatement stmt = null;
    	try {
	    	for (SubRubro subRubro : subrubros) {
	    		String del = "UPDATE subrubro SET subRubHabilitado = 0 WHERE IdSubRubro = ?";
				stmt = (PreparedStatement) conn.prepareStatement(del);
				stmt.setInt(1, subRubro.getIdSubRubro());
				stmt.execute();
			}
    	}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    }
	
	public void modificarDescripcion(SubRubro subRubro, String newDesc, Rubro r) throws Exception{
    	Connection conn = null;
    	PreparedStatement stmt = null;
    	try {
	    	conn = (Connection) abrirConexion();
	    	String upd ="UPDATE subrubro SET descSubRubro = ?, IdRubro = ? WHERE IdSubRubro = ?";
			stmt = (PreparedStatement) conn.prepareStatement(upd);
	    	stmt.setString(1, newDesc);
	    	stmt.setInt(2, r.getIdRubro());
	    	stmt.setInt(3, subRubro.getIdSubRubro());
			stmt.executeUpdate();
		}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		//conn.close();
    		cerrarConexion(conn);
    	}
    }
	
	public void modificarSubRubro(Rubro rubro, Connection conn) throws Exception{
    	PreparedStatement stmt = null;
    	try {
    		for (SubRubro sr : rubro.getSubrubros()) {
    			if(sr.getIdSubRubro() == -1){
    				agregarSubRubro(sr, conn);
    			}else {
	    			String upd = "UPDATE subrubro SET descSubRubro = ?, IdRubro = ? WHERE IdSubRubro = ?";
	    			stmt = (PreparedStatement) conn.prepareStatement(upd);
	    	    	stmt.setString(1, sr.getDecSubRubro());
	    	    	stmt.setInt(2, rubro.getIdRubro());
	    	    	stmt.setInt(3, sr.getIdSubRubro());
	    			stmt.executeUpdate();
    			}
			}
	    }
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    }
	
	public SubRubro devolverSubRubro(Integer id) throws Exception{
    	Connection conn = null;
    	SubRubro sr = null;
    	PreparedStatement stmt = null;
    	
    	try {
    		conn = (Connection) abrirConexion();
    		String sel ="SELECT descSubRubro as descripcion, IdRubro FROM subrubro WHERE IdSubRubro =  ? and subRubHabilitado = 1";
    		stmt = (PreparedStatement) conn.prepareStatement(sel);
    		stmt.setInt(1, id);
    		ResultSet result = stmt.executeQuery();
    		RubroAdapter ra = new RubroAdapter();
    		Rubro r = null;
    		if(result.next()){
    			String descripcion = result.getString("descripcion");
    			Integer idRubro = result.getInt("idRubro");
    			r = ra.devolverRubro(idRubro);
    			sr = new SubRubro(id, descripcion, r, true);
    		}
    	}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		//conn.close();
    		cerrarConexion(conn);
    	}
		return sr;
    }
	
	public List<SubRubro> devolverTodos() throws Exception{
    	Connection conn = null;
    	List<SubRubro> subRubros = new ArrayList<SubRubro>();
    	PreparedStatement stmt = null;
		try {
			conn = (Connection) abrirConexion();
			RubroAdapter ra = new RubroAdapter();
			Rubro r = null;
    		String sel ="SELECT IdSubRubro , descSubRubro as descripcion, IdRubro from subrubro WHERE subRubHabilitado = 1";
    		stmt = (PreparedStatement) conn.prepareStatement(sel);
    		ResultSet result = stmt.executeQuery();
    		SubRubro sr = null;
    		while(result.next()){
    			Integer id = result.getInt("idSubRubro");
    			String descripcion = result.getString("descripcion");
    			Integer idRubro = result.getInt("idRubro");
    			r = ra.devolverRubro(idRubro);
    			sr = new SubRubro(id, descripcion, r, true);
    			subRubros.add(sr);
    		}
		}
		catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		cerrarConexion(conn);
    	}
    	return subRubros;
    }
	
	public List<SubRubro> devolverSubRubros(Rubro rubro, Connection conn) throws Exception{
    	List<SubRubro> subRubros = new ArrayList<SubRubro>();
    	PreparedStatement stmt = null;
		try {
			String sel ="SELECT IdSubRubro , descSubRubro as descripcion, IdRubro from subrubro WHERE subRubHabilitado = 1 and IdRubro = ?";
    		stmt = (PreparedStatement) conn.prepareStatement(sel);
    		stmt.setInt(1, rubro.getIdRubro());
    		ResultSet result = stmt.executeQuery();
    		SubRubro sr = null;
    		while(result.next()){
    			Integer id = result.getInt("idSubRubro");
    			String descripcion = result.getString("descripcion");
    			sr = new SubRubro(id, descripcion, null, true);
    			subRubros.add(sr);
    		}
		}
		catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	return subRubros;
    }
	
	public List<SubRubro> devolverSubRubros(Rubro rubro) throws Exception{
    	List<SubRubro> subRubros = new ArrayList<SubRubro>();
    	PreparedStatement stmt = null;
    	Connection conn = null;
    	try {
			conn = (Connection) abrirConexion();
			String sel ="SELECT IdSubRubro , descSubRubro as descripcion, IdRubro from subrubro WHERE subRubHabilitado = 1 and IdRubro = ?";
    		stmt = (PreparedStatement) conn.prepareStatement(sel);
    		stmt.setInt(1, rubro.getIdRubro());
    		ResultSet result = stmt.executeQuery();
    		SubRubro sr = null;
    		while(result.next()){
    			Integer id = result.getInt("idSubRubro");
    			String descripcion = result.getString("descripcion");
    			sr = new SubRubro(id, descripcion, null, true);
    			subRubros.add(sr);
    		}
		}
		catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		cerrarConexion(conn);
    	}
    	return subRubros;
    }
}
